# _*_ coding:utf-8 _*_
# @File  : plan.py
# @Time  : 2021-12-21  08:20
# @Author: zizle
import pathlib
import datetime
import time
import pandas as pd

from fastapi import APIRouter, Form, UploadFile, Depends, Query, Path, Body
from pydantic import BaseModel, constr

from db import FAConnection
from db import utils as dbutils
from interfaces.depends import logged_require
from configs import FILE_STORAGE
from utils.file import generate_unique_filename
from utils.datetime_util import verify_datetime_format
from hutool.tools import datetime_handler
from status import r_status
from category import VARIETY_NAME

plan_api = APIRouter()


class PlanItem(BaseModel):
    title: constr(min_length=1, max_length=64)
    active_date: constr(min_length=10, max_length=10)
    expire_date: constr(min_length=10, max_length=10)
    category: str
    varieties: list
    apply_firm: int


@plan_api.post('/', summary='上传投资方案')
async def create_exchange_plan(person: dict = Depends(logged_require),
                               file: UploadFile = Form(...),
                               title: constr(min_length=1, max_length=64) = Form(...),
                               active_date: constr(min_length=10, max_length=10) = Form(...),
                               expire_date: constr(min_length=10, max_length=10) = Form(...),
                               build_price: float = Form(...), target_price: float = Form(...),
                               cutloss_price: float = Form(...),
                               fund: float = Form(...),
                               category: str = Form(...), varieties: list = Form(...), apply_firm: int = Form(...),
                               group_name: str = Form(...)):
    if not all([build_price, target_price, cutloss_price]):
        return {'code': r_status.VALIDATE_ERROR, 'message': '各价格不能为0！'}

    # 验证价格盈亏比等数据
    if cutloss_price - build_price == 0 or abs((target_price - build_price) / (cutloss_price - build_price)) < 3:
        return {'code': r_status.VALIDATE_ERROR, 'message': '投资方案的盈亏比需大于3:1。'}

    passed_variety = ['IF', 'IH', 'IC', 'TS', 'TF', 'T']
    exist = [True for v in passed_variety if v in varieties]
    if not exist:
        if abs(round(target_price / build_price - 1, 2)) < 0.06:
            return {'code': r_status.VALIDATE_ERROR, 'message': '投资方案的价格区间需为6%以上。'}

    if group_name not in ['宏观金融', '农业产品', '化工能源', '黑色金属']:
        return {'code': r_status.VALIDATE_ERROR, 'message': '小组归属确认失败!'}
    # 检验date数据
    if not verify_datetime_format(active_date) or not verify_datetime_format(expire_date):
        return {'code': r_status.VALIDATE_ERROR, 'message': '日期格式错误,创建失败!'}
    if not title:
        return {'code': r_status.VALIDATE_ERROR, 'message': '方案标题必填,创建失败!'}
    if not varieties:
        return {'code': r_status.VALIDATE_ERROR, 'message': '关联品种必填,创建失败!'}
    varieties = ','.join(varieties)
    # 保存文件，保存到数据库
    year_folder = datetime.datetime.today().year
    sql_folder = f'INVESTMENT/EXCHANGE_PLAN/{year_folder}/'
    file_folder = FILE_STORAGE + sql_folder
    folder, filename, suffix = generate_unique_filename(file_folder, file.filename, 'pdf', hashed=True)
    sql_path = sql_folder + filename + '.' + suffix
    filepath = folder + filename + '.' + suffix
    target_folder = pathlib.Path(folder)
    if not target_folder.exists():
        target_folder.mkdir(parents=True)
    # 保存文件
    with open(filepath, 'wb') as fp:
        content = await file.read()
        fp.write(content)
    # 插入数据
    create_sql = 'INSERT INTO exchange_plan (active_date,expire_date,title,category,varieties,apply_firm,creator,' \
                 'group_name,filepath,fund,build_price,target_price,cutloss_price) VALUES ' \
                 '(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    sql_params = [active_date, expire_date, title, category, varieties, apply_firm, person['uid'],
                  group_name, sql_path, fund, build_price, target_price, cutloss_price]
    db_conn = FAConnection(conn_name='创建投资计划')
    _, success = db_conn.insert(create_sql, param=sql_params)
    if success:
        return {'code': r_status.CREATED_SUCCESS, 'message': '创建投资计划成功!'}
    else:
        # 移除文件
        t_file = pathlib.Path(filepath)
        if t_file.exists():
            t_file.unlink()
        return {'code': r_status.SERVER_ERROR, 'message': '服务器错误,创建计划失败!'}


@plan_api.get('/', summary='查询我的方案记录')
async def exchange_plan(person: dict = Depends(logged_require), page: int = Query(1, ge=1),
                        page_size: int = Query(10, ge=1), kw: str = Query(None)):
    start, offset = (page - 1) * page_size, page_size
    if kw:
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS id,create_time,active_date,expire_date,title,category,varieties,' \
                    'apply_firm,examine_level,level_explain,ret_explain,examine_ret,group_name,filepath,plan_profit FROM exchange_plan ' \
                    'WHERE creator=%s AND is_delete=0 AND title like %s ' \
                    'ORDER BY create_time DESC LIMIT %s,%s;'
        params = [person['uid'], f'%{kw}%', start, offset]
    else:
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS id,create_time,active_date,expire_date,title,category,varieties,' \
                    'apply_firm,examine_level,level_explain,ret_explain,examine_ret,group_name,filepath,plan_profit FROM exchange_plan ' \
                    'WHERE creator=%s AND is_delete=0 ORDER BY create_time DESC LIMIT %s,%s;'
        params = [person['uid'], start, offset]
    db_conn = FAConnection('查询投资计划')
    query_ret = db_conn.paginator_query(sql=query_sql, page=page, page_size=page_size, param=params)
    level_texts = {1: '一级', 2: '二级', 3: '三级', 4: '四级', 5: '五级'}
    ret_texts = {1: '展示', 2: '实盘', 3: '外发'}
    for item in query_ret['records']:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['variety'] = ','.join([VARIETY_NAME.get(i, i) for i in item['varieties'].split(',')])
        item['firm_text'] = '是' if item['apply_firm'] else '否'
        item['level_text'] = '' if item['examine_level'] == 0 else level_texts.get(item['examine_level'], '未知')
        item['ret_text'] = '' if item['examine_ret'] == 0 else ret_texts.get(item['examine_ret'], '未知')
    return {'code': r_status.SUCCESS, 'message': '查询投资计划成功!', 'data': query_ret}


# 查询近期的方案数量记录
@plan_api.get('/recentlyCount/', summary='查询近期的方案数量记录')
async def recently_plan_count(person: dict = Depends(logged_require)):
    # current = datetime.datetime.strptime('2021-12-26', '%Y-%m-%d')
    current = datetime.datetime.today()
    # 本年
    year_start = f'{current.year}-01-01'
    year_end = f'{current.year}-12-31'
    # 本月
    month_start, month_end = datetime_handler.get_current_month_start_end(current.year, current.month)
    month_start = month_start.strftime('%Y-%m-%d')
    month_end = month_end.strftime('%Y-%m-%d')
    # 本周
    week = current.weekday()
    week_start = (current + datetime.timedelta(days=-week)).strftime('%Y-%m-%d')
    wee_end = (current + datetime.timedelta(days=6 - week)).strftime('%Y-%m-%d')
    # 查询本年数据，进行统计
    query_sql = 'SELECT id,create_time ' \
                'FROM exchange_plan WHERE is_delete=0 ' \
                'AND creator=%s AND DATE_FORMAT(create_time,"%%Y-%%m-%%d")>=%s;'
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, param=[person['uid'], year_start]))
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '查询方案计数成功!', 'data': {'year': '-', 'month': '-', 'week': '-'}}
    df['my_date'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    year_count = df[(df['my_date'] >= year_start) & (df['my_date'] <= year_end)].shape[0]  # 本年计数
    month_count = df[(df['my_date'] >= month_start) & (df['my_date'] <= month_end)].shape[0]  # 本月计数
    week_count = df[(df['my_date'] >= week_start) & (df['my_date'] <= wee_end)].shape[0]  # 本周计数
    year_count = str(year_count) if year_count else '-'
    month_count = str(month_count) if month_count else '-'
    week_count = str(week_count) if week_count else '-'
    return {'code': r_status.SUCCESS, 'message': '查询方案计数成功!',
            'data': {'year': year_count, 'month': month_count, 'week': week_count}}


# 查询近x条方案的成功率和收益率
@plan_api.get('/recentlyRate/', summary='个人近x条方案的成功率和收益率')
async def recently_plan_rate(person: dict = Depends(logged_require), count: int = Query(10, ge=1)):
    query_sql = 'SELECT id,create_time,close_time,plan_profit,fund ' \
                'FROM exchange_plan ' \
                'WHERE close_time IS NOT NULL AND is_delete=0 AND creator=%s ' \
                'ORDER BY close_time DESC LIMIT %s;'
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, param=[person['uid'], count]))
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '没有方案记录',
                'data': {'success_rate': '-', 'profit_rate': '-'}}
    # 计算成功率和收益率
    success_rate = df[df['plan_profit'] > 0].shape[0] / df.shape[0]
    profit_rate = df['plan_profit'].sum() / df['fund'].sum()
    success_rate = f'{round(success_rate * 100, 2)}%'
    profit_rate = f'{round(profit_rate * 100, 2)}%'
    return {'code': r_status.SUCCESS, 'message': '查询方案比率成功！',
            'data': {'count': df.shape[0], 'success_rate': success_rate, 'profit_rate': profit_rate}}


# 修改方案记录，审批和结果
@plan_api.put('/{plan_id}/')
async def modify_exchange_plan(person: dict = Depends(logged_require), plan_id: int = Path(..., ge=1),
                               body_data: dict = Body(...)):
    accept_column = ['examine_level', 'level_explain', 'examine_ret', 'plan_profit', 'ret_explain']
    accept_data = dbutils.exclude_columns(body_data, accept_column)
    middle_sql = dbutils.create_sql(accept_data)
    if 'plan_profit' in accept_data.keys():  # 结束时间
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        middle_sql += ',close_time=%(close_time)s '
        accept_data['close_time'] = now_time

    accept_data['id'] = plan_id
    sql = 'UPDATE exchange_plan SET ' + middle_sql + 'WHERE id=%(id)s LIMIT 1;'
    db_conn = FAConnection()
    _, success = db_conn.execute(sql, param=accept_data)

    if success:
        return {'code': r_status.SUCCESS, 'message': '保存成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '服务器错误，保存失败!'}


class PlanOrderItem(BaseModel):
    active_date: str
    description: constr(min_length=1, max_length=512)


@plan_api.post('/{plan_id}/record/', summary='上传方案记录')
async def create_plan_order(plan_id: int = Path(..., ge=1), order_item: PlanOrderItem = Body(...)):
    if not verify_datetime_format(datetime_str=order_item.active_date, f='%Y-%m-%d %H:%M:%S'):
        return {'code': r_status.VALIDATE_ERROR, 'message': '日期时间无法格式化:`%Y-%m-%d %H:%M:%S`'}
    db_conn = FAConnection(conn_name='方案交易记录')
    # 查询方案对象
    query_sql = 'SELECT id,build_time FROM exchange_plan WHERE id=%s;'
    planobj = db_conn.query(query_sql, param=[plan_id], fetchone=True, keep_conn=True)[0]
    if not planobj:
        db_conn.close()
        return {'code': r_status.VALIDATE_ERROR, 'message': '方案不存在，创建记录失败!'}

    # 创建记录
    save_sql = 'INSERT INTO exchange_plan_order (plan_id,active_date,description) VALUES (%s,%s,%s);'
    save_param = [plan_id, order_item.active_date, order_item.description]

    sql_list = [save_sql]
    param_list = [save_param]
    if not planobj['build_time']:
        # 修改建仓时间
        now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        update_sql = 'UPDATE exchange_plan SET build_time=%s WHERE id=%s;'
        update_param = [now_time, plan_id]
        sql_list.append(update_sql)
        param_list.append(update_param)

    success = db_conn.execute_tasks(sql_list=sql_list, param_list=param_list)
    if success:
        return {'code': r_status.CREATED_SUCCESS, 'message': '创建记录成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '服务器错误，创建失败!'}


@plan_api.get('/{plan_id}/record/', summary='获取方案记录')
async def get_plan_order(plan_id: int = Path(..., ge=1)):
    query_plan = 'SELECT id,plan_profit FROM exchange_plan WHERE id=%s;'

    sql = 'SELECT id,create_time,plan_id,active_date,description ' \
          'FROM exchange_plan_order WHERE plan_id=%s ORDER BY active_date DESC;'
    db_conn = FAConnection(conn_name='查询方案记录')
    plan_obj = db_conn.query(query_plan, param=[plan_id], fetchone=True, keep_conn=True)[0]
    if not plan_obj:
        db_conn.close()
        return {'code': r_status.SUCCESS, 'message': '计划不存在!', 'data': {}}

    records = db_conn.query(sql, param=[plan_id])
    for item in records:
        item['active_time'] = item['active_date'].strftime('%Y-%m-%d %H:%M:%S')
    response_data = {'records': records, 'plan_profit': round(plan_obj['plan_profit'], 2)}
    return {'code': r_status.SUCCESS, 'message': '查询计划的记录成功!', 'data': response_data}


@plan_api.delete('/{plan_id}/record/{order_id}/', summary='删除方案记录')
async def delete_plan_order(person: dict = Depends(logged_require),
                            plan_id: int = Path(..., ge=1), order_id: int = Path(..., ge=1)):
    # 检查该记录是否发布了30分钟，发布30分钟后将无法删除
    db_conn = FAConnection()
    record_obj = db_conn.query('SELECT id,create_time FROM exchange_plan_order WHERE id=%s;', param=[order_id],
                               fetchone=True, keep_conn=True)[0]
    if not record_obj:
        db_conn.close()
        return {'code': r_status.NOT_CONTENT, 'message': '数据不存在，删除失败!'}
    if (datetime.datetime.now() - record_obj['create_time']).seconds >= 30 * 60:
        db_conn.close()
        return {'code': r_status.FORBIDDEN, 'message': '距离发布时间已经过去好长一段时间，不能删除了!'}

    sql = 'DELETE FROM exchange_plan_order WHERE id=%s AND plan_id=%s;'

    _, success = db_conn.execute(sql, param=[order_id, plan_id])
    if success:
        return {'code': r_status.NOT_CONTENT, 'message': '删除成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '服务器错误，删除失败!'}
